Explanation on ACWP costs in DWH_INV_ASSIGN_PERIOD_FACTS
search cancel

Explanation on ACWP costs in DWH_INV_ASSIGN_PERIOD_FACTS

book

Article ID: 444198

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

In the table DWH_INV_ASSIGN_PERIOD_FACTS you found ACWP Cost on resource level that does not match. 

  • The values are inconsistent as you are not not posting ACWP Cost from timesheets on Labor Resources
  • Timesheets don’t get posted to WIP. Only Expenses are posted to WIP
  • The team is using this table to do reports and they questioned why the field is filled for some of those
  • Actual Hours are correct. ACWP Cost is not and how is it populated?

Environment

All Supported Clarity Releases

Resolution

The costs are posted as expected as Clarity relies on the scheduled Rate Matrix Extraction job and that with Post Timesheets is what posts the Labor costs.

Here is the flow of the Actual Cost posting to the Labor resource:

  1. Rate Matrix extraction runs
    • When RME runs, it looks at the financial Cost/Rate Matrix attached to the project&resources.
    • It extracts all those rates and flattens them into NBI_PROJ_RES_RATES_AND_COSTS and the internal rate curves tables for specific date ranges
    • At this point, the system knows exactly what the cost rate is for that resource on any given day.
  2. The timesheet is approved and Post Timesheets runs
    • Post Timesheet job posts the Actual Hours to the assignment in table PRASSIGNMENT
    • While it is posting the hours the job automatically looks up the pre-calculated cost rate generated by the RME job in Step 1.
    • The system performs the calculation: Timesheet Hours × Cost Rate.
  3. The Assignment Actual Cost is updated
    • The result of that calculation is immediately written to the assignment in two places:
    • PRASSIGNMENT.ACTUAL_COST The total actual cost on the assignment
    • PRJ_BLB_SLICES in the assignment::actcost_curve time-slices for the specific days the work occurred
  4. NOTE:  This happens during timesheet posting & it does not wait for financial processing.
  5. Load Data Warehouse runs and updates DWH_INV_ASSIGN_PERIOD_FACTS
    • It reads the assignment's actual cost curve directly from PRJ_BLB_SLICES from Step 3
    • It inserts that value into DWH_INV_ASSIGN_PERIOD_FACTS.ACWP_COST.

How to prevent the data from being updated?

Possible Workarounds

FUTURE TIMESHEETS:

Option 1: Use $0 Row for Labor in the Rate Matrix

Leave the Rate Matrix on the project but add a specific row (or rows) that matches the Labor resources and sets their cost to 0.

  1. Look at how the Rate Matrix columns are configured. Confirm which Class is assigned to Labor resources versus Expense resources.  
  2. In the Rate Matrix have a row where the condition matches the Labor resources ONLY
  3. Leave the existing rows for Expense resources exactly as they are.
  4. Run the Rate Matrix Extraction job so the system calculates the new $0 rate for labor.

When the Post Timesheets job runs for a labor resource, it will find the $0 row in the matrix. It will calculate Hours × $0 = $0, so $0 is added onto the actual cost curve, but Expense resources will still get their normal rates.

Option 2: Remove Financial Properties from Labor Resources

If Labor resources are never used in financial plans and their timesheets never go to WIP you may not need them to be financially enabled.

  1. Go to the Resource profile - Properties – Financial and disable them from being financially enabled.

NOTE: This may affect other cost fields as ETC Cost / Allocation Cost. Only remove it if you DO NOT need any costs for labor

Current Records in PAST

Doing these workaround is only for the cost for future timesheets. The timesheets that have already been posted have already updated the actual cost in the assignment curve so this won’t change

Possible Approaches:

  1. Timesheet Adjustments: Return the historical timesheets and have them resubmitted and reapproved while the new $0 cost rate is active (this will be very complex task and is not recommended for over 10 timesheets)
  2. Database Update (Unsupported): Directly update the PRASSIGNMENT.ACTUAL_COST and PRJ_BLB_SLICES (actcost_curve) tables to 0 via SQL by copying from another line that is set to 0.
  3. Do not fix historical Timesheets, inform the users and adjust the reports. Filter out RESOURCE_TYPE_KEY = 0 in the Data Warehouse in the Jaspersoft report or do not include ACWP cost for labor resources in the SQL query used for the reports
Powered by Wolken Software