APPENDIX B - PROCESSING DETAILS

Custom Tables and Custom Fields in the Template Defined by Deltek

Which fields in MS Project are affected by the interface?

Two MS Project fields are affected by the interface: Actual Work and Actual Cost.  The interface updates the timescale section of the usage views (Resource Usage or Usage by Resource). It places one lump sum for a task/resource combination in this timescale area as the summary for the gathered period, based on the end date of the period. MS Project then summarizes those values and puts them in the Actual Work and Actual Cost fields. If you select multiple periods for the update process, the program summarizes each period and stores it separately. For information about the sources of the costs, refer to the "Where Do Actual Costs/Hours come from?" section.

What happens during the Setup Process?

When you perform the setup process and map your MS Project Resources to Costpoint resources, the mapping information is saved in the custom fields provided by Deltek.  If you are using the Template (or a copy of it), those fields are grouped in the custom table called Costpoint in the Resource side of table collection.

If you are not using the Deltek-supplied template (in which case you must have followed the instructions in Appendix A), you do not have this table defined. However, the custom fields to make up this table are defined. Refer to "Create or modify a table" in the MS Project Help facility to create the table so that the fields are visible.

The setup process will create the following custom fields that are required for the mappings if they are not yet created:

Note that you do not have to have this table in your MS Project file (MPP) to run the interface. However, the existing mappings are preserved in this table so that you do not need to re-map each time you run the interface; instead, simply enter any changes or additions.

What happens during an Update?

The update process imports values from the Costpoint worktables created by the Gather/Prepare step, using the date supplied when you selected the Import data through date in the Import screen. If the As of Date in any row in the worktable is the same as this date, or earlier, that row is selected. The As of Date is the Costpoint Subperiod Ending Date for the data that was gathered. You can select and gather data for multiple periods and process them with one update. 

During the import process, the program makes three passes:

• The first pass verifies that the projects selected for processing exist in MS Project by examining the WBS column in MS Project for the Costpoint project ID. If it does not exist, you have the option to add it.

• Another pass is for labor processing, where the program selects rows with an Account Function Code of "2" in the worktable, and uses the labor mappings to match resources.

• The last pass is for non-labor, where the program selects rows with Account Function Code of "3," and uses non-labor mappings to match resources.

The program skips rows without a matching resource. It examines rows with matching resources if their matching resources have been assigned to a specific task in the MS Project. If not, they are assigned to the task.

After the assignments are done, the program places current period hours and costs in the worktable into the timescale columns in MS Project. It places one lump sum for a task/resource combination in this timescale area as the summary for the gathered period, based on the end date of the period. MS Project then summarizes those values and puts them in the Actual Work and Actual Cost fields. If you select multiple periods for the update process, the program summarizes each period and stores it separately.  

Other fields are customized and added to MS Project by the update process. If you are using the Deltek-supplied template (or a copy), you can find the Costpoint table in the Project side of MS Project as shown:

The following custom fields are updated by the update process:

You do not have to have this table in your MS Project file (MPP) to run the interface, but it will allow you to check what constitutes Actual Costs. Depending on your choice of burden type, the program uses different fields to generate Actual Cost. The system creates these fields during the update process (if they are not already present). If the table is not already defined, refer to "Create or modify a table" in the MS Project Help facility.

Where Do Actual Costs/Hours come from?

This topic illustrates:

1.         How actual costs are computed from the work file created by the Gather/Prepare phase of the interface processing.

2.         How the values in the worktables are generated for the View Gathered tab of the interface after the Gather/Prepare phase is done.

MS Project "Actual Cost" Calculation

Actual

Target

No burden

T&M

Gathering the Data

The following describes how the Gather process gathers data from Costpoint and puts it in the Z_CP_GATHER table (a worktable within Costpoint). You can see the gathered information in the View Gathered Data tab.

Three different types of rows are created from LAB_HS (Labor History), PROJ_SUM (Project Summary), and PSR_PY_SUM (Project Summary Report Prior Year Summary). These are: Labor with no burden, Non-labor with labor burden, and non-labor with non-labor burden. For each type, the application selects data for:

• The Current Period (where the FY is the same as the fiscal year entered on the screen, the PD_NO is the same as the period entered on the screen, and the SUB_PD_NO is earlier than, or the same as, the sub-period entered),

• Year-to-Date (where the FY is the same as the fiscal year entered and the PD_NO is earlier than the period entered), and

• The Prior Year (where the FY is earlier than the fiscal year entered).

LAB_HS

Labor with no burden is summarized by:

PROJ_ID/ORG_ID/ACCT_ID/BILL_LAB_CAT_CD/GENL_LAB_CAT_CD

This information is selected from all rows in the LAB_HS table and coded as ACCT_FUNC_CD =  "2".  Also, the dollar values in T&M projects are zeroed out (keeping hours intact).

PROJ_SUM AND PSR_PY_SUM

Non-labor with labor burden is summarized by:

PROJ_ID/ORG_ID/ACCT_ID/L1_ACCT_SEG_ID

This information is selected from ACCT_FUNC_CD = ("2" or "11") in PROJ_SUM and PSR_PY_SUM and is coded as ACCT_FUNC_CD = "3."

PROJ_SUM AND PSR_PY_SUM

Non-labor with non-labor burden is summarized by:

PROJ_ID/ORG_ID/ACCT_ID/L1_ACCT_SEG_ID

This information is selected from ACCT_FUNC_CD = ("3" or "12") in PROJ_SUM and PSR_PY_SUM (PY) and is coded as ACCT_FUNC_CD = "3."

Summary of Sources of Values in the Gather Process

#1  = labor with no burden.

#2  = non-labor with labor burden.

#3  = non-labor with non-labor burden.

Note: Account Function codes of gathered data are assigned as follows:

"2" = Direct Labor costs from #1 above

"3" = ODC and Burden from #2 and #3