Reconfiguration of matrix columns causes the application to select an incorrect matrix row
search cancel

Reconfiguration of matrix columns causes the application to select an incorrect matrix row

book

Article ID: 21871

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

If a Matrix is created with some columns and rows are created, then subsequently, the columns are reconfigured, with a specific sequence of steps the existing matrix rows are not removed and if the existing rows remain, they may cause the application to select an incorrect matrix row.  Also, sometimes you might see the columns 'jump' positions when you are trying to add a new column to the matrix column configuration.  The steps below are one way to simulate the corrupted column order sequence; there may be other steps to produce the same symptoms.  

Note: This is reproduced on versions 12.x up thru 13.2, however, if you upgrade to a later release, you may still experience this issue if it existed before the upgrade and you did not discover it.  This is because, although the issue is resolved in 13.3, the data in the table for the column order is not 'fixed' with the upgrade.  See the Solution section for details on how to 'fix' the existing column order data.  

Use the following query to see if the 'columnno' sequence is correct:

SELECT * FROM PPA_MATRIXCOLDEF 
ORDER BY MATRIXKEY, COLUMNNO ; 
 

Steps to Reproduce:

  1. Log in to the application as an Administrator user 
  2. Administration, Finance: Manage Matrix
  3. Click 'New' Button, Matrix Name = 'MyMatrix100'
    Click 'Save and Continue' button
  4. Assign Columns, Select an attribute such as 'Charge Code'
  5. Click 'Save and Continue' button
  6. Edit Matrix Rows,  Click 'New' button
  7. Create 2 or more rows with different values for the 'Charge Code'
  8. Test the matrix by creating a cost plan using the 'population' feature or by posting a transaction.  The correct rate should be selected as expected.
  9. Assign Columns, Following the exact steps:
    1. Keep the 'Charge Code' attribute in the list of assigned columns
    2. Select the 'Resource Role' attribute and assign to the matrix
    3. (Both attributes are now in the 'Selected' box)
      ** DO NOT ** Click 'Save' or 'Save and Continue' button yet
    4. Move the 'Charge Code' attribute back to the 'Available' box
      (Now only 'Resource Role' is in the 'Selected' box)
    5. Click 'Save' button

  10. Edit Matrix Rows, the rows created in Step 5 have not been removed. The rows look like duplicates now because the 'Charge Code' column that originally made each row unique is now removed and the 'Resource Role' column shows an asterisk (*) because the rows existed at the time the column was added to the matrix configuration
  11. Modify the existing rows to now make them unique with different 'Resource Role' values
  12. Add a few more unique rows with Resource Roles
  13. Create financial transactions and check which rate is selected
  14. Create Detailed Financial Plans and check which rate is selected

Expected Result: Matrix configuration should be updated correctly.

Actual Result: Matrix rows have residual data from previous configuration.

Environment

Release: 13.2
Component: Clarity Financial Management

Resolution

Resolution:

CLRT-64605: Resolved in Clarity 13.3
The resolution fixes the problem when the administrator is changing the Matrix Column configuration as described in the summary. Resolution for this issue does not fix corrupted data. Use the specific steps documented in the workaround to resolve posted transactions with incorrect rates.

Workaround:

Removing the old rows does not resolve the issue.  The issue is in the table that holds the matrix column definition.  The column order sequence is corrupted. 

If you query the PPA_MATRIX, PPA_MATRIXCOLDEF and PPA_MATRIXVALUES tables you will see the column is removed, but in the values table, the reference to that column is there causing the Rate Matrix evaluation action to find an incorrect rate.

The COLUMNNO column needs to be correctly populated in PPA_MATRIXCOLDEF table. This table represents the assigned columns for a Matrix.
There should not be any gap in COLUMNNO values for any given matrix as it represents the sequence of selected assigned columns.
When the same submit operation has both add and remove actions as mentioned in the steps to reproduce, it leaves gaps in COLUMNNO values in PPA_MATRIXCOLDEF table thereby corrupting the Matrix.

For example, in a configuration that had 3 columns where one is removed and one is added in the same step, before saving, you may see the numbered columns, like 1,2,4 in the column definition table; it jumps over one column. This will cause the application to pick the wrong rate with a seemingly random value because the columns are not sequentially numbered (missing a column identification).

There are 2 methods to fix the corrupted data. (You can do this in an upgraded version as well and it will work to resolve the issue)  

Method #1: For small matrices associated with a few projects

  1. Create a new Matrix - update all references to point to the new matrix 
  2. Remove all references to the existing Matrix 
  3. Delete existing Matrix; it is corrupted  
    If you get the following message, it indicates the Matrix is still referenced on an investment: REVMGR-20217:Error removing matrix entry.  
  4. Execute 'Rate Matrix Extraction' job for a 'Full Run' ; selecting options (1*),2,3 - (Extract*-optional, Prepare, Update) 
  5. If there are existing transactions already Posted to WIP, create WIP adjustments to recalculate rates. If transactions have not been posted yet, they will get the new rate at the time the transactions are Posted to WIP    

Method #2: Update the existing matrix using XOG

The Rate Matrix XML Open Gateway (XOG) WRITE Action will REPLACE THE ENTIRE ROWS -- Therefore you must ensure ALL rows are in the XOG file; if any of the rows are missing, they will be removed. 

Using the following steps will allow the XOG READ to get all rows in the matrix and just update it. 

  1. Query the table and see the sequence is incorrect:  SELECT * FROM PPA_MATRIXCOLDEF ORDER BY MATRIXKEY, COLUMNNO 
  2. XOG READ the existing matrix using 'matrices_read.xml' sample file 
  3. XOG WRITE the matrix back in 
  4. Query the table again and see the sequence is now correct: SELECT * FROM PPA_MATRIXCOLDEF ORDER BY MATRIXKEY, COLUMNNO 
  5. Execute Rate Matrix Extraction job with Options #2, #3 (and #1 if desired ; it is optional) 
  6. If there are existing transactions already Posted to WIP, create WIP adjustments to recalculate rates. If transactions have not been posted yet, they will get the new rate at the time the transactions are Posted to WIP
Powered by Wolken Software