Error on Approving Submitted Budget / Staffing issue due to PRTEAM.PRROLEID =0

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When populating a plan, because it is looking at the role on the team which now in the database has a PRTEAM.prroleid = 0 the cost plans pick that up as well. If there is another line in the plan when grouping by role the error will occur when trying to approve the budget.  

This issue also causes refreshing on Staffing Grid when grouping by Role due to the fact PRROLEID =0 (Zero) in PRTEAM table.

Steps to Reproduce:

The steps require there end up being two lines on the cost plan using 2 grouping  attributes (TRANSCLASS, ROLE)   with one grouping attribute being the same (TRANSCLASS) and one by role where one role_id is null and one role_id = 0

1. Use 2 financially enabled resources both having the same TRANSCLASS
   •  1 who had a primary role
   •  1 who does not have a primary role 
2. Since we are going to post a manual transactions for this resource which essentially is an 'unplanned' line on the cost plan;
         a.  Use a financially active project but make sure the resources  are not already on the team
              - make sure there is a task 
         b.  Create 2 transactions, you can use the checkbox to preserve data but will change the resource
              - do not add a role to the transactions
        c.  Post the transactions
3. Run Import Financial Actuals job so the transaction posts back to the project and adds the resource to the team and the assignment.
4. Check the prroleid in PRTEAM for both resources

Expected Results:  PRTEAM.prroleid is null for both resources you added to the team

Actual Results:  PRTEAM.prroleid = 0 for the resource who had no primary/investment role. This then creates an error when Approving a Submitted budget

Environment

Clarity 16.1.x, 16.2.x

Cause

DE80564

Resolution

DE80564 is targeted to be fixed in 16.2.3 (August 2024) and also backported to 16.2.2 patch 1

Workaround: Without the fix deployed, ensure that either the transaction has a role_id, or the resource itself has a primary role.

Additional Information

In fin_cost_plan_details if a role_id = 0 and there is another grouping row in the table where the role_id is null, upon approving a submitted budget an error is shown and the budget cannot be approved.

In the app-ca logs following error stack can be seen

ORA-01427: single-row subquery returns more than one row

This is a result of creating a manual transaction for a resource who 

•  Has no primary role
•  No investment role
•  Is not already on the team
• And having another resource with one of the same grouping attributes.