Problem:
CA PPM 14.2/14.3 upgrade fails with error ORA-01438: value larger than specified precision allowed for this column while inserting value in PFM_INV_CONTRAINTS
Steps to Reproduce:
- Start the upgrade to either CA PPM 14.2 or 14.3
Expected Result: The upgrade should complete successfully
Actual Result: The upgrade fails with the below error
1/16/15 10:42 PM (admin) Executing Ant script: /fs0/clarity1/clarity/upgrade/14.2.0/component/postupgrade/pfm.xml
11/16/15 10:42 PM (admin) Create investment constraint sums for portfolios
11/16/15 10:43 PM (admin) com.niku.dbtools.ant.ExecutableException: /fs0/clarity1/clarity/upgrade/14.2.0/component/postupgrade/pfm.xml:27: com.niku.union.persistence.PersistenceException:
11/16/15 10:43 PM (admin) SQL error code: 1438
11/16/15 10:43 PM (admin) Error message: ORA-01438: value larger than specified precision allowed for this column
11/16/15 10:43 PM (admin) Executed:
11/16/15 10:43 PM (admin) insert into pfm_inv_constraint_sums (portfolio_id
11/16/15 10:43 PM (admin) , plan_id
11/16/15 10:43 PM (admin) , instance_id
11/16/15 10:43 PM (admin) , ph_curve_sum
11/16/15 10:43 PM (admin) , code
11/16/15 10:43 PM (admin) , ph_curve
11/16/15 10:43 PM (admin) , created_date
11/16/15 10:43 PM (admin) , created_by
11/16/15 10:43 PM (admin) , last_updated_date
11/16/15 10:43 PM (admin) , last_updated_by)
11/16/15 10:43 PM (admin) values(?,?,?,?,?,?,?,?,?,?)
Environment:
Applies to all supported PAS environments for specified releases.
Cause:
Caused by CLRT-79116
This issue is caused by the size of the column. In source table(FIN_FINANCIALS) size of the columns is (32, 6) and in destination table(PFM_INV_CONSTRAINT_SUMS) size of the column(PH_CURVE_SUM) is (16,6).
Resolution:
The Sustaining Engineering team is reviewing this issue.
Workaround:
Note: The following workaround is provided by our Sustaining Engineering team for On Premise Customers. If you implement the changes to the code file may be overwritten with subsequent patches or upgrades. Be sure to review this developer, pre-approved customization to the code. Once the issue is resolved in new version, be sure to check and verify the new files are installed and overwrites the existing code files.
If you are a SaaS Customer, our upgrade team will take care of this issue.
Interim code modification to upgrade successfully
For On Premise implementations, you can follow the steps to update the application code file prior to CA PPM 14.2 or 14.3 and then start the upgrade
In case of upgrade, i.e. coming from 13.x to 14.2 then need to repackage the 14.2 or 14.3 installer
- Extract 14.2 or 14.3 installer
- Navigate to ..\install-packages\14.2.0
- Extract package.jar and locate the file PFM_INV_CONSTRAINT_SUMS.xml in location \upgrade\database\schema\table
- Update length as 32 for column PH_CURVE_SUM like below:
<Column name="PH_CURVE_SUM" dataType="numeric" length="32" precision="6" nullable="true"/> - Re-package the package.jar and remove the extracted files from package.jar
- Re-Run the upgrade
Note: Before re-running the upgrade process, please roll-back to a working CA PPM version