Now, the part (a) error is caused by a DB constraint that is conflicting with how hibernate perform batch update.
1. Check if the constraint is there: SELECT * FROM user_constraints WHERE table_name = 'PPM_FISCAL_PERIODS_NLS' AND constraint_name = 'PPM_FISCAL_PERIODS_NLS_U1';
2. if the constraint exists, drop the constraint ALTER TABLE ppm_fiscal_periods_nls DROP CONSTRAINT ppm_fiscal_periods_nls_u1;
3. Drop the index associated with constraint (if this throws an error that index no longer exist it is safe to skip..) DROP INDEX ppm_fiscal_periods_nls_u1;
4. Recreate the contraint to only validate after commit ALTER TABLE PPM_FISCAL_PERIODS_NLS ADD (CONSTRAINT PPM_FISCAL_PERIODS_NLS_U1 UNIQUE (START_DATE, END_DATE, PERIOD_TYPE) INITIALLY DEFERRED DEFERRABLE);
5. Backup the ppm_fiscal_periods_nls
6. Retry running sh ./KGenFiscalPeriods.sh shift
Hardware and software can always be replaced, but your data may be irreplaceable! It is better to make a backup before make changes.
If this still an issue please rollback all these steps, upgrade to a supported version of PPM (PPM 8 or PPM9) and create a case with us.
If you find that this post resolves your issue, please be sure to mark it as an accepted solution. Please give me kudos if the information is useful.
The PARENT_PERIOD_ID and CHILD_PERIOD_ID in knta_period_groups should have some values .
For example, period_group_id 30000 is Q1 1998, the parent_period_id is 30240, which it should refer to the period_id 30240 in knta_periods table, which has the period full name of "1998". Similarily, period_group_id 3000 has child_period_id of 30180, this is the id in knta_periods, which has period full name of "Q1 1998".
In short, the parent_period_id of quarter refer to the period_id of the year. The child_period_id is itself.
It seems that you had improperly imported data in to the schema. Clean up the corrupted data before regenerating fiscal periods. This can be done by exporting the data, cleaning up the records, and re-importing. These steps are documented in Portfolio Management Configuration Guide, Appendix B “Changing Fiscal years and Periods”.
Followed your recommendations and got different error message (see below). We are upgrading to 9.x soon but for now we are stuck with 7.5. Really appreciate your help. I am with Enterprise Systems at the University of Florida.
As I explained before the he PARENT_PERIOD_ID and CHILD_PERIOD_ID in knta_period_groups should have some values .
Checking the last data attached I can see that now these columns have been populated with the correct data, however we still need to remove the period_group_ids from 30240 to 30299, since these should not exist in the knta_period_groups table
Here are the steps:
1. stop the server 2. backup the data in the table knta_period_groups 3. Run the following query against the PPM schema
delete from knta_period_groups where period_group_id between 30240 and 30299;