The interface to Costpoint will transfer project budget, project budget revisions, forecast, and ETC revenue data into Costpoint from a comma-delimited ASCII file produced by Project Planner (or from any budgeting tool that can produce a comma-delimited file with the required information).
The file produced may vary in format from client to client; therefore, you can define the physical structure and the information content contained within the input (source) file. This definition allows for the various types of data and the multiple levels of detail within the Costpoint budgeting system. The only exception is project budget revisions, which have a predefined file structure (see sample data definition scripts in Appendix A or B).
The application supports the following:
Period values by "Forecast or Budget" for:
- Direct Labor Costs
- Direct Non-Labor Costs
- Direct Labor Costs and Indirect Costs
- Direct Non-Labor Costs and Indirect Costs
- Manual Fees
Total values by "Forecast or Budget" for:
- Direct Labor Costs
- Direct Non-Labor Costs
- Direct Labor Costs and Indirect Costs
- Direct Non-Labor Costs and Indirect Costs
- Manual Fees
ETC revenue amounts
Project Budget Revisions
Since the content and format of the file varies by client, you can map your input file columns (source) to Costpoint table/columns (target). The target columns presented depend on the budgeting option in effect and the content of the input file that you defined. Note that we currently support standard project budgets only. You cannot re-map target columns for project budget revisions; instead, the process uses the pre-defined mapping to process these files.
There may be situations where the input file does not contain all of the data required based on the budgeting option in effect and the type of budget data in the file. Therefore, you can define the source location of data at mapping time, and you can have different data sources. The primary source of data is the input file, but you can also use default values for columns that are defined at mapping and/or to indicate columns whose values will be entered at import time.
There may also be extra lines in the input file, such as subtotal and total rows, that do not represent detailed budget information and should not be imported into the Costpoint database. You can include or exclude rows by defining a column number and a data value for that column. You can define multiple inclusion or exclusion rules for an input file definition.
In certain situations, the input file will contain columns that represent the same type of information (e.g., dollars) for different accounting periods where the starting accounting period represented by the first column of this type, and the number of times this column occurs within the input file, can vary from upload to upload. In this situation, the application will prompt you for a starting fiscal year and period and compute the remaining periods based on the definition of the column.
The column definition contains the following information:
Whether or not it is a repeating column.
The interval between columns (currently, always "1").
The number of times the column occurs within the input file.
You can run the import process in interactive or process server mode. The application captures any required runtime input and stores the results as an item in the function parameters catalog for later use by the process server.
The assumed process flow is as follows:
In the Standard Source File Information tab of the Interface Configuration screen, set up the source file definition, the row inclusion and exclusion parameters, the source file row definition, and the source file column definition. When you save the source file column definition, the application builds the base mapping definition. You can view the mapping and source file structure that has been pre-defined for project budget revisions, but you cannot change it from the Interface Configuration screen.
The Mapping tab of the Interface Configuration screen displays a list of the columns you defined in one grid and the Costpoint columns in a mapping definition grid. At this time, define the source from which the expected data is coming for each Costpoint column in the Column Source field.
If the Column Source is "F" (input file), you must provide a source column number.
If the Column Source is "D" (Default value), you must provide a default value.
If the source of the data is to be obtained during import, provide no column number or value at this time. The application will prompt you during the Import Project Budgets phase for any column where the Column Source is "R" (Run-time).
If the Column Source is "C" (Computed), the application will provide the appropriate value. The use of "C" as a source of data in this release is supported for the following columns:
Synthetic Key - Project Forecast Direct
Date and Time record was updated
Costpoint User ID
Update Control Counter
Last Update Date
ORG_ID. The ORG_ID can be retrieved from the PROJ table and loaded into the input data if none has been provided in the input file.
During the import process, the application uses the input file, the run-time input values, and the mapping definition to validate and populate the proper Costpoint tables, depending on the Costpoint version and the budgeting option in effect.
Data is first parsed/imported into worktables and validated. Valid data is staged in worktables; erroneous data is staged into an error table. The application generates an error report for any invalid data. Several reports are available that you can use to see the valid data before updating Costpoint.
When you run the import under the Process Server, the application automatically imports all valid data. If it encounters errors, the application saves an error report to a file: c:\PplannerErrRpt.RTF.
Project Manager: Uses Project Planner to develop budget, forecast, and ETC data.
Data administrator: Exports data from Project Planner.
Costpoint staff: Runs Costpoint processes to import and update budget, forecast, and ETC tables.
Importing into Costpoint
Data administrator - Provide input file to Costpoint staff.
Costpoint staff
Interface Configuration screen
Define source file definition, row inclusion and exclusion rules, row definitions, column definitions and mapping definitions.
Import Project Budgets screen
Select appropriate input files.
Select appropriate project option.
Select appropriate interface setup definition.
Provide runtime data inputs.
Submit job to process server and review results of process server run or run interactively to review reports and import.
Processing within Costpoint after import.
Costpoint staff
Budgets and forecasts
Run the Update Project Total Budget screen in Costpoint Budgeting and ETC to reflect detail provided.
Run the Maintain Budget Report Tables screen in Costpoint Budgeting and ETC and select "Update" in the Select Operation drop-down box.
You must be familiar with the content and format of any input file before attempting to create any interface configurations.
The data administrator must provide one set of input files per project to the Costpoint staff. A set consists of direct and indirect labor costs, direct and indirect non-labor costs, manual fees, and ETC revenue
If you do not use the same input file format across PMs, you must create a separate source file definition to handle separate formats.
The project budget/forecast revisions must exist in Costpoint before you import budget data. You can import the revisions using the Budget Revisions option in the Import Project Budgets screen.
Only Comma-Separated Value (CSV) formatted files will be processed.
You must create all input files using the MULTIPLE COLUMN option (i.e., repeating columns that represent accounting periods) in Enterprise Planner.
If the number of repeating column occurrences varies from import to import (i.e., the accounting time dimensions covered by the input file are decreasing or increasing), you need to either set up a source file definition that matches each of the expected number of column occurrences, which vary depending on the expected importing intervals and/or periods imported, or you need to change the number of occurrences defined within one selected source file definition. For example, if you import annually and semi-annually, the annual import is for 12 periods, and 12 repeating columns will be in the input file (assuming only one year's worth of data is in the input file and a year is made up of 12 periods). The semi-annual import is for 6 periods, and 6 repeating columns will be in the input file. If the number of repeating column occurrences remains constant for each import, you do NOT need to modify the source file definition. It is critical to know the number of expected columns in order to verify that there is sufficient data within an input file and to know the number of accounting time dimensions to manufacture.
Repeating columns must run in consecutive order.
The Project, Organization, and Account columns will be parsed, if necessary. This data must be separated from any description data that may exist in the same column. A space is the assumed separator.
The amounts can be both positive and negative. Negative amounts are formatted with a leading minus sign and decimal point, with no commas (e.g., -1000.00).
The Projects, Organizations, and Accounts must be in the proper Costpoint format and at the appropriate level for PSR reporting.
In order to zero out amounts for a preexisting row in a table, submit a record in the input file with the amounts being zero.
Default values are NOT validated until import time, since in many cases it is the combination of a default value, a run-time value, and an input file value that must be validated, rather than the default value alone.
The Subperiod must always be a default value, and that value must be "1."
The Data provided at run time applies to all input files selected and to all projects within the selected input files.
All insertions or updates to tables summarize the data rows provided. The application does NOT check for duplicate data. This is necessary since the data provided in the input file may be at a level below the correct level for posting.