pepdwill Honored Contributor.
Honored Contributor.
83 views

Error Deleting Staffing Profile

Hi,

 

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
0 Likes
1 Reply
Natalia_R_PPM Absent Member.
Absent Member.

Re: Error Deleting Staffing Profile

Hi Pepdwill.

 

 

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
(
position_forecast_id NUMBER,
position_id NUMBER,
forecast_date DATE
)

3. Created the following trigger to populate the temp table with the BEFORE INSERT/UPDATE values

create or replace
TRIGGER del
BEFORE INSERT OR UPDATE
ON rsc_position_forecast
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN
insert into del select :new.position_forecast_id, :new.position_id, :new.forecast_date from dual;
END;

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:

CUSTOMER
“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 )
values('DEMAND_THRESHHOLD','-1','Demand Threshhold');
commit;

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

 

Thanks &

 

Best Regards,

Natalia Rojas

 

HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.