Clarity: NBI_PROJ_RES_RATES_AND_COSTS table retains records which should be deleted

Document ID : KB000048227
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

If you create a project and add Team Staff Members, the Rate Matrix NBI_PROJ_RES_RATES_AND_COSTS table is populated with rows for this team member as expected. If the Team member does not have any actual transactions, and the resource is removed from the team, you would expect the Rate Matrix Extraction job to remove the rate records because the Team Record no longer exists in the system. This leaves orphaned data in the table cluttering up the space in the table. This could also pose a potential performance issue with executing the job for a 'Full Run' based on the customer configuration of the matrix columns and the amount of data configured on projects.

This issue only happens when there are no team records at all for the project. For the first example, only one resource is added to the team and then removed ; therefore there are no team records for this project. In the second example, the entire project is removed and yet the matrix rows remain in the matrix table, orphaned.

Steps to Reproduce - Case #1:

Only one resource, removed, no team records for this project

  1. Create a Project and add a Resource to the Team
  2. Execute the 'Rate Matrix Extraction' job for a Full Run
  3. Query the Matrix Table and the Team Table using the internal project ID (ie. 5000000)
    SELECT * FROM NBI_PROJ_RES_RATES_AND_COSTS WHERE PROJECT_ID = ?
    SELECT * FROM PRTEAM WHERE PRPROJECTID = ?
  4. Go to the Project, Team and remove this resource (no actuals have been posted, so it can be done)
  5. Query the Team Table - the records are removed as expected
  6. Execute the 'Rate Matrix Extraction' job for a Full Run
  7. Query the Matrix Table again to see if the records have been removed

Expected Result: The records should be removed

Actual Result: The records remain in this table.

Steps to Reproduce - Case #2:

Entire project is deleted, team records are deleted too

  1. Create a Project and add a Resource to the Team
  2. Execute the 'Rate Matrix Extraction' job for a Full Run
  3. Query the Matrix Table and the Team Table using the internal project ID (5000000)
    SELECT * FROM NBI_PROJ_RES_RATES_AND_COSTS WHERE PROJECT_ID = ?
    SELECT * FROM PRTEAM WHERE PRPROJECTID = ?
  4. Do not remove the resource, but instead, close the project and mark it inactive
  5. Execute the 'Purge Financial Tables' job, selecting this project
  6. Mark the Project for Deletion
  7. Execute the 'Delete Investments' job, selecting this project
  8. Execute the 'Rate Matrix Extraction' job for a Full Run
  9. Query the Matrix table to see if there are orphaned records:
    SELECT * FROM NBI_PROJ_RES_RATES_AND_COSTS WHERE PROJECT_ID NOT IN (SELECT PRPROJECTID FROM PRTEAM)

Actual Result: Although the Team and the Project has been removed, the records remain in the Rate Matrix Table.

Solution:

WORKAROUND:

Having the orphaned records will not have an impact on the functionality for calculating costs, however, it may have an impact if you are referencing the data for user-defined reporting or portlets. You may elect to clean up the table. Test in a non-production system to identify the orphaned records and remove them from this table.

STATUS/RESOLUTION:

This issue has been documented as CLRT-74093 and is in review with development.

Keywords: CLARITYKB, CLRT-74093, clarity13open.