Clarity Timesheet status does not change to Adjusted after adjustment is posted

Products

• Clarity PPM On Premise

Issue/Introduction

A specific Timesheet did not change status after adjustment. How to fix this and would the fix have any potential billing impact for the existing data (when timesheet PRSTATUS was 4 for both the original and new adjusted timesheet).

Steps taken:

• User created a timesheet and submitted it for approval.
• Resource Manager approves it.
• "Post Timesheet" job runs and status of the timesheet changes from "Approved" to "Posted".
• Same timesheet is opened for adjustment. An adjustment timesheet gets created.
• Timesheet is edited and submitted for approval.
• Timesheet is approved. Status of adjustment timesheet changes from "Submitted" to "Approved".
• "Post Timesheet" job runs.

Expected Behavior:

Adjustment timesheet should replace the original timesheet. 

Status of the adjustment timesheet should changed to "Posted".

Status of original timesheet should change to "Adjusted".

Actual Behavior:

Adjustment timesheet replaces original timesheet but status of both adjustment and original timesheet remains "Posted"

Environment

Clarity 16.4.1

Cause

The `PRADJUSTEDID` column on the **adjustment** timesheet holds the `PRID` of the **original** timesheet. This is the key linkage the Post Timesheets job should use to set the original's PRSTATUS=5. This issue occurs when the Post Timesheets job fails to traverse the internal link (PRADJUSTEDID) between the adjustment record and the original record. While the job successfully posts the new hours, it fails to execute the final status flip on the original PRTIMESHEET row.

Resolution

Ran a SQL update statement against PRTIMESHEET table to update the orignal timesheet with Adjusted status (prstatus =5).

UPDATE PRTIMESHEET SET PRSTATUS = 5 WHERE PRID = [Original_Timesheet_PRID];

About the question on the possible impact of the fix and actions to be considered as a best practice:

 

Effect on DWH/Datamart
 
**This may be the most significant area that may be requiring action.**

The `DWH_TIMESHEET_V` source view maps `PRTIMESHEET.PRMODTIME` as `CLARITY_UPDATED_DATE`:
 

The DWH incremental sync uses `CLARITY_UPDATED_DATE` as its change-detection watermark.
 
The SQL update only changed `PRSTATUS` and did not also update `PRTIMESHEET.PRMODTIME`, the DWH sync will not detect the row as changed** and `DWH_TME_SHEET.TIMESHEET_STATUS_KEY` will remain `4` for the original timesheet.

This directly drives the resource time period hours materialized view DWH_X_RES_TIME_PER_FACTS_MV.xml
 
''
FROM ppm_dwh.DWH_TME_SHEET TS
INNER JOIN ppm_dwh.DWH_TME_ENTRY TE ON TS.TIMESHEET_KEY = TE.TIMESHEET_KEY
INNER JOIN ppm_dwh.DWH_TME_ENTRY_FACTS T ON TE.TIMEENTRY_KEY = T.TIMEENTRY_KEY
INNER JOIN ppm_dwh.DWH_CMN_PERIOD_MAPPING DP ON (T.PERIOD_KEY = DP.DAILY_PERIOD_KEY AND DP.WEEKLY_PERIOD_KEY > 0)
INNER JOIN ppm_dwh.DWH_CFG_AUDIT A ON A.TABLE_NAME = 'DWH_RES_PERIOD_FACTS'
WHERE TS.TIMESHEET_STATUS_KEY = 4
"

This view feeds `DWH_RES_PERIOD_FACTS` (resource period hours for weekly, monthly, and fiscal periods). The filter `TIMESHEET_STATUS_KEY = 4` means:

- **Before the SQL fix AND before DWH sync:** Both timesheets in the DWH have `TIMESHEET_STATUS_KEY = 4` → hours are double-counted in `DWH_RES_PERIOD_FACTS` and any dashboard/report built on it.
- **After the SQL fix but before DWH sync:** The PPM source is corrected, but DWH still has the stale status → **double-counting persists in all DWH-based reports**.
- **After DWH sync runs (once PRMODTIME is also updated):** `DWH_TME_SHEET.TIMESHEET_STATUS_KEY` for the original timesheet becomes 5, it drops out of the `= 4` filter, and `DWH_RES_PERIOD_FACTS` will be correct.

1. **Actions required:**
   If your SQL update did **not** also update `PRMODTIME`, run a follow-up:
   ```sql
   UPDATE PRTIMESHEET
   SET PRMODTIME = SYSDATE -- or GETDATE() on SQL Server
   WHERE PRID = <original_timesheet_prid>
   AND PRSTATUS = 5;
   ```
2. Then run the **Load Data Warehouse** job (Datamart Extraction) to sync the change into `DWH_TME_SHEET`.
   
   
3. After the sync, `DWH_RES_PERIOD_FACTS` will self-correct on the next run of the **Resource Period Facts** load.
    
   
   

Effect on Financial Management / Billing (WIP):
 
**This depends on whether the issue affected the WIP reversal logic, not just the PRSTATUS update.**

In Clarity, when an adjustment timesheet is posted, the Post Timesheets job is supposed to:

1. Post the new adjustment entries as WIP transactions in `PPA_WIP`
2. Reverse the original timesheet's WIP entries (the delta/reversal entries)
3. Set the original timesheet to PRSTATUS=5

Siince the issue manifested as step 3 not happening. The critical question is: **did step 2 also fail?**
- If step 2 succeeded (WIP reversals were written) but only step 3 failed (PRSTATUS not flipped), then billing/WIP data is correct, and your SQL fix is the only action needed.
- If step 2 also failed (no WIP reversals were created for the original timesheet's entries), then both the original and adjustment entries exist in `PPA_WIP` with positive quantities → double billing exposure.

**Verification query to run against `PPA_WIP`:**
```sql
SELECT TRANSNO, TRANSDATE, QUANTITY, STATUS, REFNO
FROM PPA_WIP
WHERE EMPNO = '<resource_code>'
AND TRANSDATE BETWEEN <period_start> AND <period_end>
ORDER BY TRANSDATE, TRANSNO;
```
Look for: matching reversal entries (negative quantities) that offset the original timesheet's entries. If you only see positive entries with no corresponding negatives, the WIP reversal was skipped and needs to be addressed by contacting Broadcom Support or manually creating the reversal WIP entries.

Effect on Project/Task/Assignment Actuals

The actuals in `PRASSIGNMENT` and `PRTASK` are almost certainly correct and unaffected by the SQL workaround.*

No additional job is needed to re-reconcile assignment actuals. The SQL fix to PRSTATUS has no retroactive effect on already-posted entry curves.
 

Conclusion:
 
The SQL workaround was validated as both accurate and necessary. To complete the process, please ensure the following actions are taken:

• Update PRMODTIME on the modified row to trigger Data Warehouse (DWH) change detection.

• Execute the Load Data Warehouse job to synchronize the status change and ensure reporting accuracy.

• Audit the WIP table for the specific resource and period to verify that no double-billing occurred.