Understanding Integration of PeopleSoft Project Costing with Microsoft Project
This topic lists prerequisites and discusses:
PeopleSoft Project Costing and Microsoft Project integration.
Copying data from PeopleSoft Project Costing to Microsoft Project.
Copying data from Microsoft Project to PeopleSoft Project Costing.
Note: As of FSCM Update Image 48, the integration with Microsoft Projects and PeopleSoft Project Costing is no longer supported. The Microsoft Integrator, in its current state, is still available, and customer can continue to use the functionality at their own discretion.
Please note that all menu options (CREFs), objects, and source code remain on your system. As of FSCM Update Image 48 PeopleSoft does not support the integration with Microsoft Projects with all past, current, or future PeopleSoft Update Image.
The PeopleSoft Project Costing integration is intended for use with Microsoft Project, desktop edition only. Projects residing in Microsoft Project Server must be saved locally by means of Microsoft Project Standard prior to integration. Before running the Microsoft Project Integration for the first time, you must:
Install the PeopleSoft - Microsoft Project Integrator on the network.
See PeopleSoft Financials, ESA and Supply Chain Management Product-Specific Installation Instructions located on Oracle's My Oracle Support website.
Specify the Third-Party Data Exchange default options on the Installation Options - Project Costing Integration page.
See Installation Options - Project Costing Integration Page.
The PeopleSoft - Microsoft Project Integrator is an application that must reside on at least one computer that is available on the network in order for integration to take place. The computer on which it resides requires these two components:
FTP service or access to an FTP share directory.
Microsoft Project application.
See PeopleSoft Financials, ESA and Supply Chain Management Product-Specific Installation Instructions located on Oracle's My Oracle Support website.
Integrating PeopleSoft Project Costing and Microsoft Project combines the PeopleSoft Project Costing project accounting capabilities with the Microsoft Project planning and scheduling features. The integration enables the PeopleSoft Project Costing and PeopleSoft Program Management applications to incorporate scheduling information for use across the enterprise and enables project managers to update their individual Microsoft Project files with actual costs, resources, schedules, and so forth from the PeopleSoft Enterprise applications.
The Microsoft Project integration allows for the transmission of data from PeopleSoft software to Microsoft software and vice versa. When you integrate, you can export data from PeopleSoft Project Costing to a Microsoft Project file or import data from a Microsoft Project file into the PeopleSoft system. You can run the integration either direction at any time, depending on the needs of the business and the project management methodology.
By using the integration of PeopleSoft Project Costing and Microsoft Project, you can synchronize:
Projects.
Project teams.
Activities.
Duration and date calculations.
Percent-complete calculations.
Budgets.
Actual costs.
Dependencies and constraints.
Additionally, if PeopleSoft Program Management is installed, the integration enables you to create and maintain assignments for labor, material, and assets at both the project and activity levels by using the Program Management functionality of the Resources, Resource Detail, and Resources by Activity pages.
The PeopleSoft Project Costing and Microsoft Project integration allows for the creation of projects in either application. The integration also allows for the transfer of tasks (in Microsoft Project) or activities (in PeopleSoft Project Costing) and their associated resource assignments. Microsoft Project's user-definable field objects store specific PeopleSoft Project Costing fields such as business unit and project ID. Microsoft Project also stores the PeopleSoft Project Costing project and activity definition, description, status, manager, percent complete for the activity, budgets, dates, and duration.
Data Flow
Four ways are available to integrate project data between PeopleSoft software and Microsoft software:
|
Integration Procedure |
Flow of Data |
|---|---|
|
Create a project in Microsoft software from an existing PeopleSoft project. |
Export from PeopleSoft software to Microsoft software. |
|
Update a Microsoft Project with data from PeopleSoft software. |
Export from PeopleSoft software to Microsoft software. |
|
Update a PeopleSoft project with data from Microsoft software. |
Import from Microsoft software to PeopleSoft software. |
|
Create a project in PeopleSoft software from an existing Microsoft project. |
Import from Microsoft software to PeopleSoft software. |
This diagram is a high-level view of the integration data flow:
This table lists the types of data that are exchanged and the direction of the data flow:
|
Data Type |
Direction |
|---|---|
|
Project data. |
Bidirectional. |
|
Project team (resources). |
Bidirectional if PeopleSoft Program Management is not installed. |
|
Activity or task. |
Bidirectional. |
|
Team or task assignments. |
Bidirectional if PeopleSoft Program Management is installed. |
|
Job codes. |
PeopleSoft to Microsoft from project team if PeopleSoft Program Management is not installed. |
|
Cost and bill rates. |
Bidirectional if you use override rates. |
|
Budget items. |
Bidirectional. |
|
Actual costs. |
PeopleSoft to Microsoft. |
|
Budget costs. |
Microsoft to PeopleSoft. |
|
Work. |
PeopleSoft to Microsoft. |
|
Dates. |
Bidirectional. |
|
Percent complete (activity level). |
Bidirectional. |
|
Percent complete (project level if calculation method is manual). |
Microsoft to PeopleSoft. |
|
Duration. |
Bidirectional. |
|
Milestones. |
Bidirectional. |
Terminology
This table describes terms that are relevant to the integration between PeopleSoft Project Costing and Microsoft Project:
|
Term |
Description |
|---|---|
|
Activity |
A project component that the system uses to capture charges that relate to that aspect of the project, and a representation of the work that is required to complete part of a project. In Microsoft Project, activities are called tasks. |
|
Actual Work |
Tracks the actual number of hours spent by a labor resource on an activity. PeopleSoft Project Costing sends actual work information to Microsoft Project from rows in the Project Transaction table that have a system source of BEX, which identifies rows that came from PeopleSoft Expenses. The Expenses to Project Costing Application Engine process (PC_EX_TO_PC) updates actual work in PeopleSoft Project Costing. Actual work is not updated from Microsoft Project to PeopleSoft Project Costing. |
|
Resources |
People, equipment, and material that are required for completing the project and the activities that are on the project. Resources can have rates associated with them for the purposes of deriving their estimated and actual costs to an activity and a project. |
|
Start Date |
A required field that represents the current start date of the project and activity. The start date can be entered in PeopleSoft software or Microsoft software. Given the duration and end date of a project or activity, the system can calculate the start date. |
|
End Date |
A required field that represents the current end date of the project and activity. The end date can be entered in PeopleSoft software or Microsoft software. The end date can be calculated if values are available for the duration and start dates. |
|
Baseline Start Date |
The planned beginning date of a project or activity. This field can be entered in PeopleSoft software or Microsoft software. |
|
Baseline Finish Date |
The planned completion date of a project or activity. This field can be entered in PeopleSoft software or Microsoft software. |
|
Actual Start Date |
The date on which the project or activity actually begins. It can be entered in PeopleSoft software or Microsoft software based on progress information that you enter. |
|
Actual Finish Date |
The date on which the project or activity actually finishes. |
|
Late Start Date |
Represents the latest date that an activity or task can start without delaying the finish of the project. This date is based on the task's start date, the late start and late finish dates of predecessor and successor tasks, and other constraints. |
|
Late Finish Date |
Represents the latest date that an activity or task can finish without delaying the finish of the project. This date is based on the task's late start date, the late start and late finish dates of predecessor and successor tasks, and other constraints. This field can be entered in PeopleSoft software or Microsoft software. |
|
Early Start Date |
Represents the earliest date that an activity or task can possibly begin, based on the early start dates of predecessor and successor tasks, and other constraints. This field can be entered in PeopleSoft software or Microsoft software. |
|
Early Finish Date |
Represents the earliest date that an activity or task can possibly finish, based on early finish dates of predecessor and successor tasks, other constraints, and any leveling delay. This field can be entered in PeopleSoft software or Microsoft software. |
|
Duration |
The total number of days that are needed to complete an activity from start date to end date. Duration is stored as hours and appears as days. Duration exists at both the project and activity level. Duration can be calculated if values are available for the project or activity start and end dates. |
|
Work |
The total number of hours spent or to be spent by a resource on an activity. Work does not equal duration. For example, the expected duration of an activity might be five days, but the work that is assigned to the activity might be 80 hours with two resources working on the activity. You define work in hours at the activity level. |
|
Remaining Work |
Remaining work, also known as estimate to complete (ETC), can be either automatically calculated or manually entered. When calculated, (ETC) = (work) − (actual work). |
Note: The PeopleSoft Integration Architecture for Microsoft Project Integrations document that is available on Oracle's My Oracle Support website discusses how the PeopleSoft and Microsoft systems exchange data fields and the business logic behind the exchange.
PeopleSoft - Microsoft Project Integrator
When the integration process runs, the Microsoft Project file that is associated with the PeopleSoft project is transferred, through FTP, to a shared directory that is accessible by the Integrator server. The Integrator performs all reading and writing to the Microsoft Project file. When a user requests an integration, data is staged for the Integrator to start. The Integrator checks every 15 seconds for the staged data. If a user attempts an integration while the Integrator is engaged with a previous request, the new request is placed in a queue until an Integrator is available. You can set up more than one Integrator to enable multiple integrations to occur simultaneously.
An Integrator has several states:
Hold: Indicates that a previously available integrator has been taken offline.
Waiting: Indicates an idle state.
The Integrator is ready and waiting for staged data to appear to perform an integration.
Busy: Indicates that the Integrator is working on a previous integration request.
Error: Indicates that the last integration has a nonrecoverable error.
In this case, the Microsoft Project file or the PeopleSoft project is not updated. The messages that are generated by the error are stored with the project. The next time that the Integrator checks for data, the error information that appears at the bottom of the Maintain Project with Microsoft page is cleared and the Integrator continues on to the next integration request.
The state of the Integrator, warnings, and error messages appear in the Integration Session Log that you can view on the Create Project from Microsoft page.
This topic discusses issues that are related to creating or updating a project in Microsoft Project with data that exists in PeopleSoft Project Costing.
Project Data
When you're creating a project from PeopleSoft software to Microsoft software, project-level data is stored in the Microsoft custom properties to validate future updates. This data includes project origin—PeopleSoft or Microsoft—business unit, project ID, project type, project status, duration, project manager name and employee ID, project manager start and end dates, and project manager effective date.
Activity Data
PeopleSoft activity-level data is synchronized with the Microsoft Project tasks. Activity data copied or updated includes activity ID or task name, activity type, description, start and finish dates, percent complete, duration, summary flag, and milestones. User text type fields are used to store key information for use in future updates. For example, the unique ID that is used for each activity business unit, project ID, and activity ID in the PeopleSoft system is stored in the Microsoft Project Task.Text20, Task.Text21, and Task.Text22 (and alsoText30) fields, respectively, when you are synchronizing activities and tasks. If the value of Text30 is altered or deleted, future updates are inaccurate.
If you use PeopleSoft Program Management, the activity date cascade calculation method that you define in the PeopleSoft system is exported to Microsoft Project based on the mappings in this table:
|
PeopleSoft Activity Date Cascade Calculation Method Exported to Microsoft |
Calculation Method Converted in Microsoft |
|---|---|
|
Manual |
Manual |
|
Delay Calculations Until Save |
Manual |
|
Realtime Calculations |
Automatic |
See Establishing PeopleSoft Program Management Business Unit Options.
Note: If the autocalculation option is selected when you export data to the Microsoft system, the dates and duration data that are sent to the Microsoft system may not remain intact. This is due to the many internal calculations that Microsoft Project makes to new or altered data.
Work Breakdown Structure
The WBS ID (work breakdown structure ID) is specified on the PeopleSoft activity definition and is copied to the Microsoft system when you export activity definitions. The WBS ID in the PeopleSoft system and the Microsoft system is a concatenation of the parent outline number and activity outline number, and cannot be modified manually. Changes that you make to a project WBS structure are reflected in the Microsoft system during the next integration process.
The PeopleSoft - Microsoft Project Integrator moves tasks by cutting and pasting. Activities that you delete in the PeopleSoft system are not automatically deleted in the Microsoft system.
Dependencies and Constraints
If you use PeopleSoft Program Management and export activity definitions from the PeopleSoft system to the Microsoft system, the integration process copies activity dependencies and constraints. During the export process, the system deletes the existing dependencies in Microsoft Project and creates new dependencies based on the dependency data sent from the PeopleSoft system.
During the export process, the PeopleSoft system also sends activity constraint information such as the deadline date, constraint type, and constraint date.
See Understanding Integration of Dependencies and Constraints with Microsoft Project.
Team Members and Resource Assignments
The contents of the project team list can be copied into the Microsoft Project resource sheet. Resources that are on the project activity team can be assigned in the Microsoft Project resource assignments. If activities and resources are copied to Microsoft Project, the integration process creates Microsoft resource assignments if the assignments in the PeopleSoft project activity team match a task and resource in Microsoft Project.
The resource's cost rate and bill rate are from the Cost Rate field (COST_RATE) and Bill Rate field (BILL_RATE) in the Project Team Member table (PROJECT_TEAM) in PeopleSoft Project Costing if you use PeopleSoft Program Management. If you use PeopleSoft Project Costing without PeopleSoft Program Management, the resource's cost and bill rates are from the Rates by Role table (PC_RATE_ROLE). The resource's cost rate is stored in the Resources.StandardRate field in Microsoft Project. The resource's bill rate is stored in the Resources.Cost1 field. Job codes do not exist in the resource sheet.
You can export the Budget Item field (PC_BUDGET_ITEM) and Project Role field (PLAN_PROJROLE) values from the project level in PeopleSoft Project Costing to the resource sheet in Microsoft Project. The Microsoft Project system stores these values in custom text field Resources.Text10 and Resources.Text9. The project role is exported to Microsoft Project only for labor resources.
If you use PeopleSoft Program Management, values from the Units field in the assignment schedule in the Resource Details page copy to the resource sheet in Microsoft Project.
As resources are copied to the resource sheet, the system creates a unique ID and stamps it in the Resource ID field (RES_UID) in the Project Team Member table for future updates. If you remove a team member or delete an assignment, it is not reflected in the Microsoft system.
Transaction Data
Transactions and resource data are sent to the Microsoft system in the following way:
Actual Cost Rows: Rows in the Project Transaction table that have actual cost analysis types are summarized by activity and copied to Microsoft Project as actual cost values at the task level.
Resource amount is the summarized field. The Actual Cost (Task.ActualCost) field is updated on the matching task in the Microsoft system.
Actual Work Rows: Rows in the Project Transaction table that have actual cost analysis types and an employee ID are summarized by activity and copied to Microsoft Project as actual work values at the task level.
Resource Quantity is the summarized field. The Work field (Task.Work) is updated on the matching task in the Microsoft system.
This topic discusses issues that are related to creating or updating a project in PeopleSoft Project Costing with data existing in Microsoft Project.
Project Data
When copying a project from Microsoft Project to PeopleSoft Project Costing, you enter the business unit, project ID, project description, integration template, project type, project status, project charging level, and project tree information. These values are used to create the project in the PeopleSoft system.
Task and WBS Data
The Microsoft Project tasks are synchronized with PeopleSoft activity-level data. The integration process imports summary and nonsummary tasks to the PeopleSoft system from the Microsoft system. Updated activity data includes activity ID, activity type and description, start and finish dates, percent complete, duration, summary flag, and milestones. New activities are formatted in the Activities Interface staging table (INTFC_ACT_GEN) with an activity ID of NEXT, while existing activities are formatted with the PeopleSoft Project Costing activity ID.
Regardless of the method that you use in the Microsoft system to calculate start and end dates for summary activities, the system assigns a calculation method of Manual in the PeopleSoft system for tasks that you import from the Microsoft system.
The WBS ID field on the task specifies the placement of activities within the WBS. The WBS ID field in Microsoft Project maps to the WBS ID in PeopleSoft Project Costing. The system verifies that the WBS structure for a project in Microsoft Project contains no more than nine levels, which is the maximum number of levels in PeopleSoft software. If a WBS contains more than nine levels, a message appears indicating that you cannot import the WBS into the PeopleSoft system. If the WBS ID in the Microsoft system contains a prefix, such as PC1.1, PC1.2, and so on, the system removes the prefix prior to populating the WBS ID field in the PeopleSoft system.
The Load Projects and Activities Application Engine process (PC_INTF_GEN) creates any new activities imported from Microsoft Project and renumbers the WBS ID on existing activities as needed to match the Microsoft Project WBS.
If you use PeopleSoft Program Management, you can select an option on the Microsoft Integration Options page for new projects created from the Microsoft system to copy the Microsoft WBS only up to the project charging level that is specified on the Create Project from Microsoft page in the PeopleSoft system.
See Understanding Project Charging Levels.
Tasks that you delete in Microsoft Project are not automatically deleted in PeopleSoft Project Costing during the integration process. For deleted tasks, the next time that you import Microsoft data to the PeopleSoft system, the system:
Assigns a WBS level 1 to the deleted tasks.
Moves the deleted tasks to the end of the project WBS structure.
Changes the activity processing status to Inactive.
Dependencies and Constraints
If you use PeopleSoft Program Management and import tasks from the Microsoft system to the PeopleSoft system, the integration process copies task dependencies and constraints. During the import process, the system deletes the existing dependencies in the PeopleSoft system and creates new dependencies based on the dependency data received from the Microsoft system.
You must enter dependency lag time in days in Microsoft Project to integrate with the PeopleSoft system. The integration process does not support percentage lag durations or elapsed time.
You cannot import dependencies for summary activities from the Microsoft system to the PeopleSoft system.
See Understanding Integration of Dependencies and Constraints with Microsoft Project.
Team Members and Resource Assignments
When you create a project, all resources and assignments that are on the Microsoft Project resource sheet can populate the Project Team Member table, and resources that are assigned to tasks in Microsoft Project can be moved to the Project Activity Team table. Entries in the Resource Name field should be either <First Name Last Name>, such as Tom Jones, or a resource requirement entry, such as Java Developer. The Resource Name field value is validated against the employee names that are in the PeopleSoft system, and if a valid name match is located, the appropriate employee is added to the project or activity. If you use PeopleSoft Program Management and no match is found, the resource from the Microsoft system is added as a generic resource in Program Management. If you use PeopleSoft Project Costing without PeopleSoft Program Management and no match is found, the resource is not imported.
The Resource Unique ID from the Microsoft resource (RES_UID) is stored in the Project Team Member table to facilitate future updates to Microsoft Project. The Resource Unique ID and the Task Unique ID (ACT_UID) are stored in the Project Activity Team table to facilitate future updates to Microsoft Project.
The integration process imports the project role values from the resource sheet in Microsoft Project to the project team, activity team, and schedule level in PeopleSoft Program Management. If you use PeopleSoft Project Costing without PeopleSoft Program Management, the system imports the project role from the resource sheet in Microsoft Project to the project team in Project Costing. If the import process updates an existing project, the project role values from Microsoft Project override the existing values in the PeopleSoft system. If no project role exists for the resource in Microsoft Project, or if the project role on the resource sheet is not valid in PeopleSoft Project Costing, the system assigns the default project role that you specify on the Microsoft Integration Options page. The system validates imported project roles against the Project Role table (PROJ_ROLE).
If you use PeopleSoft Program Management and PeopleSoft Resource Management, you can select integration options to:
Automatically add a named resource with a status of Requested if manager approval is required, or Committed if manager approval is not required, and generate a PeopleSoft Resource Management assignment if you import a resource from Microsoft Project that exists as a resource in Resource Management.
If you import a named resource from Microsoft Project that is not an existing resource in PeopleSoft Resource Management, the system adds the resource to the Resources page in PeopleSoft Program Management as a generic resource. If you import a named resource from Microsoft Project that is an existing resource in PeopleSoft Resource Management, the Resource Management values for resource pool, region, personnel status, and primary project role populate the Resources page for the resource.
Automatically add a generic resource with a status of Requested and generate a PeopleSoft Resource Management allocation if you import a resource from Microsoft Project that does not exist as a resource in Resource Management.
If you import a generic resource from Microsoft Project that belongs to a resource group (known as a resource pool in PeopleSoft software) that does not match a resource pool in PeopleSoft Resource Management, the system adds the generic resource with a resource pool value of Unassigned. The system imports the Microsoft Project values for resource name and region. The integration process does not re-create PeopleSoft Resource Management assignments or allocations that are already requested if you subsequently import the same project from Microsoft Project.
If a pending or approved assignment request already exists for the resource, the system updates the assignment schedules only for resources with a status of Considered.
Disregard PeopleSoft Resource Management schedule conflicts and create assignments even if doing so results in overbooking resources.
Select a default region from which to request generic resources.
When task dates change, all resources and resource assignments are copied to the PeopleSoft system, and a routine is called to adjust the dates, if needed, and to create new entries in the team tables during updates.
If you use PeopleSoft Program Management, values from the Units field in the resource sheet in Microsoft Project copy to the assignment schedule in the Resource Details page.
Resources or assignments that you delete from the Microsoft system are not automatically deleted in the PeopleSoft system during the next import process. Assignments must be manually deleted from the PeopleSoft system.
Transaction Data
The system identifies budget rows in Microsoft Project if a value is in the BaselineCost field at the task level. Each task is examined. If the task is not a summary and its BaselineCost value is not zero, the system copies the row to the budgeting tables if Projects Budgeting is enabled. If Projects Budgeting is disabled, the system copies the row to the Project Transaction table. The PeopleSoft - Microsoft Project Integrator selects the analysis types based on the budget analysis group. Changes to the task BaselineCost can be reflected in future updates.
If Projects Budgeting is enabled, the Microsoft Project budget periods for an activity are unlimited from the project start date. Budget rows for activities with start dates that are outside of this date range are not imported to budgeting tables or the Project Transaction table. The system copies the total amount from the budget periods in the Microsoft system to the start date period in PeopleSoft Project Costing.
The integration process imports the budget items from the resource sheet in Microsoft Project to the project team, activity team, and schedule level in PeopleSoft Program Management. If you use PeopleSoft Project Costing without PeopleSoft Program Management, the system imports the budget item from the resource sheet in Microsoft Project to the project team in Project Costing. If the import process updates an existing project, the budget item values from Microsoft Project override the existing values in the PeopleSoft system. The system validates imported budget items against the Budget Category Control table (GM_BUD_CATEGORY).
If no budget item exists for the row in Microsoft Project, or if the budget item on the resource sheet is not valid in PeopleSoft Project Costing, the system assigns the default budget item that you specify on the Microsoft Integration Options page. The system assigns a budget item to budget rows for activities based on the default Material Budget Item if either of these conditions exist:
The task contains at least one material resource row from Microsoft Project.
The task does not contain any resource rows.
If a task contains only labor resource rows in Microsoft Project, the system assigns a budget item to budget rows for those activities based on the default Labor Budget Item that you specify on the Microsoft Integration Options page.