Error Deleting Staffing Profile
When trying to delete a Cancelled free-standing staffing profile - we get receive an error, and the following exception is recorded in the log files. Can anyone help?
GUID D2C92887-407A-43DD-855D-5D774A047D74 Name org.springframework.dao.DataIntegrityViolationException URL http://ppm.com/itg/staffing/DeleteStaffingProfile.do?null Reason
Could not execute JDBC batch update; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (PPMPROD.RSC_POSITION_FORECAST_U1) violated at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:85) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:947) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222) [wrapped] java.sql.BatchUpdateException: ORA-00001: unique constraint (PPMPROD.RSC_POSITION_FORECAST_U1) violated at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:566) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1248) at oracle.jdbc.driver.OraclePreparedStatement.sendBatch(OraclePreparedStatement.java:3685) at oracle.jdbc.driver.OraclePreparedStatementWrapper.sendBatch(OraclePreparedStatementWrapper.java:1427) at sun.reflect.GeneratedMethodAccessor6674.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.mercury.itg.core.persistency.PPMOracleHibernateBatcher.invoke(PPMOracleHibernateBatcher.java:104) at com.mercury.itg.core.persistency.PPMOracleHibernateBatcher.doExecuteBatch(PPMOracleHibernateBatcher.java:258) at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246) [wrapped] org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
Re: Error Deleting Staffing Profile
I was researching about this issue and I found one similar case.
They explain the following:
It looks like the BO installation added a UNIQUE index RSC_POSITION_FORECAST_U1 in RSC_POSITION_FORECAST table manually. Because of this UNIQUE incex they started having a problem when modifying the start date or deleting a staffing profile.
They researched the deletion issue more and here’s what is causing it:
1. The table RSC_POSITION_FORECAST has a unique index RSC_POSITION_FORECAST_U1 on fields POSITION_ID, FORECAST_DATE
2. When a SP is deleted, PPM first sets the POSITION_ID field to NULL and only then deletes it
3. Therefore, if the SP has more than one position with overlapping dates (FORECAST_DATE) then setting the POSITION_ID to NULL will cause an attempt to violate the index (to confirm it, I’ve performed this update manually in my lab and was able generate the same error)
How did they arrive at that?
1. The table RSC_POSITION_FORECAST has the following unique index:
CREATE UNIQUE INDEX "KINTANA"."RSC_POSITION_FORECAST_U1" ON "KINTANA"."RSC_POSITION_FORECAST" ("POSITION_ID", "FORECAST_DATE")
2. Created a temp table
create table del
3. Created the following trigger to populate the temp table with the BEFORE INSERT/UPDATE values
create or replace
BEFORE INSERT OR UPDATE
FOR EACH ROW
insert into del select :new.position_forecast_id, :new.position_id, :new.forecast_date from dual;
4. Queried the temp table and saw the NULL values in POSITION_ID
Then, I looked into the second issue with staffing profiles GM is having (error message thrown after importing SP positions and then changing the start date of the project). Although it is a separate issue from the end user’s perspective and displays a different error message than the deletion SP issue, it also occurs due to the index RSC_POSITION_FORECAST_U1 (when the project start date is changed, HP shifts the dates of the SP positions and you have the problem as below).
I am assuming RnD added this index as part of the BO installation in order to expedite reading data out of this table and into the CDC. Therefore, I ask customer to left the index in place but made it NON-UNIQUE.
After that, the BO ETL finished without issue after index change to NON-UNIQUE. It didn't affect BO.
They also recommends the following:
"Change index RSC_STAFF_PROF_ALLOCATION to NONUNIQUE. If the unique index of RSC_STAFF_PROF_ALLOCATION(ASSIGNMENT_ID, ALLOCATION_DATE) has the same issue, you can drop the unique index and create a NON-UNIQUE index on RSC_STAFF_PROF_ALLOCATION(ASSIGNMENT_ID, ALLOCATION_DATE)."
However, after this problem the check the PPM and BO tables and they founded some data discrepancies:
“I’ve made that index NON UNIQUE as well.
What we’re seeing, probably as a result of these issues, is a mismatch between these two tables in BO vs PPM (see attached).
These discrepancies are a showstopper for BO, since RM reporting is key for them.
They know about data discreancies after change unique index to non-unique.
Please do the following steps to resync unsynced data.
Here is a solution which synchronizes whole RSC_POSITION_FORECAST and its related fact tables.
1, (Optional, only when rsc_position_forecast is huge ) In BO, disable all indexes of rsc_position_forecast.
2, In BO,
truncate table rsc_position_forecast;
insert/*+append*/ into rsc_position_forecast
select * from rsc_position_forecast@<DB_LINK_TO_PPM>;
insert into rpt_params(parameter_name, parameter_value, description )
3, Run incremental ETL manually
4, (Optional) In BO, enable all indexes of rsc_position_forecast.
5, In BO
delete from rpt_params where parameter_name='DEMAND_THRESHHOLD';
Also other recommendation was:
1) Run FULL ETL again (re-install BO), after reinstall BO and the problem has dissapeared.
Hope this information is helpful
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.