Script ‘drop_75_o​bjects.sql​’ causes problems during upgrade

End of support has almost been reached for PPM 7.5. There will still be some candidates for upgrades out there that are currently planning upgrades to 8.0 or 9.1. Please be aware of the following situation that may occur:

After upgrade from 7.5 to to 8.0 and further to 9.1, the following views and packages are shown as being invalid.

the view KCST_PROJ_CUMULATIVE_CM_V
the view PM_TASKS_LIST_V
the view PM_TASKS_LIST_NO_PRJ_SEC_V
the package KRSC_EFFORT_DECOMPRESSION
the package KCST_COST_UTIL

Root cause is that the optional upgrade script drop_75_objects.sql is removing some objects which should not be removed.

For:
the view KCST_PROJ_CUMULATIVE_CM_V
the view PM_TASKS_LIST_V
the view PM_TASKS_LIST_NO_PRJ_SEC_V

Cause:
The table KCST_BUDGET_LINKED_ENTITIES is referenced.
However, the table is removed in the upgrade while it is present in a fresh install.
To solve this, please do the following:
Please create a blank table:

CREATE TABLE "<schema_owner>"."KCST_BUDGET_LINKED_ENTITIES"
(
"BUDGET_LINKED_ENTITY_ID" NUMBER NOT NULL ENABLE,
"CREATION_DATE" DATE NOT NULL ENABLE,
"CREATED_BY" NUMBER NOT NULL ENABLE,
"LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
"LAST_UPDATED_BY" NUMBER NOT NULL ENABLE,
"ENTITY_LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
"BUDGET_ID" NUMBER NOT NULL ENABLE,
"ACTIVE_FLAG" VARCHAR2(1 CHAR) NOT NULL ENABLE,
"ENTITY_ID" NUMBER,
"ENTITY_PRIMARY_KEY" NUMBER
)
Commit;

For:
the package KCST_COST_UTIL package

Cause:
The table KCST_BUDGETS is referenced.
Please check if you have the table KCST_BUDGETS present in your database? If not, we’l have to recreate it similar to the above script.

CREATE TABLE "<schema_owner>"."KCST_BUDGETS"
(
"BUDGET_ID" NUMBER NOT NULL ENABLE,
"CREATION_DATE" DATE NOT NULL ENABLE,
"CREATED_BY" NUMBER NOT NULL ENABLE,
"LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
"LAST_UPDATED_BY" NUMBER NOT NULL ENABLE,
"ENTITY_LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
"BUDGET_NAME" VARCHAR2(100 CHAR) NOT NULL ENABLE,
"STATUS_CODE" VARCHAR2(30 CHAR) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(250 CHAR),
"PARENT_BUDGET_ID" NUMBER,
"INITIATION_REQUEST_ID" NUMBER,
"START_PERIOD_ID" NUMBER NOT NULL ENABLE,
"END_PERIOD_ID" NUMBER NOT NULL ENABLE,
"SHOW_ACTUALS_FLAG" VARCHAR2(1 CHAR) NOT NULL ENABLE,
"USER_DATA_SET_CONTEXT_ID" NUMBER,
"USER_DATA1" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA1" VARCHAR2(250 CHAR),
"USER_DATA2" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA2" VARCHAR2(250 CHAR),
"USER_DATA3" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA3" VARCHAR2(250 CHAR),
"USER_DATA4" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA4" VARCHAR2(250 CHAR),
"USER_DATA5" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA5" VARCHAR2(250 CHAR),
"USER_DATA6" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA6" VARCHAR2(250 CHAR),
"USER_DATA7" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA7" VARCHAR2(250 CHAR),
"USER_DATA8" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA8" VARCHAR2(250 CHAR),
"USER_DATA9" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA9" VARCHAR2(250 CHAR),
"USER_DATA10" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA10" VARCHAR2(250 CHAR),
"USER_DATA11" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA11" VARCHAR2(250 CHAR),
"USER_DATA12" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA12" VARCHAR2(250 CHAR),
"USER_DATA13" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA13" VARCHAR2(250 CHAR),
"USER_DATA14" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA14" VARCHAR2(250 CHAR),
"USER_DATA15" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA15" VARCHAR2(250 CHAR),
"USER_DATA16" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA16" VARCHAR2(250 CHAR),
"USER_DATA17" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA17" VARCHAR2(250 CHAR),
"USER_DATA18" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA18" VARCHAR2(250 CHAR),
"USER_DATA19" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA19" VARCHAR2(250 CHAR),
"USER_DATA20" VARCHAR2(250 CHAR),
"VISIBLE_USER_DATA20" VARCHAR2(250 CHAR),
"ENTRY_PERIOD_TYPE_ID" NUMBER NOT NULL ENABLE,
"ACTUALS_ROLLED_UP_CODE" VARCHAR2(30 CHAR) DEFAULT 'MANUAL' NOT NULL ENABLE,
"REGION_ID" NUMBER DEFAULT -1 NOT NULL ENABLE,
"CAPEX_OPEX_ENABLED_FLAG" VARCHAR2(1 CHAR) DEFAULT 'Y' NOT NULL ENABLE,
"SYNCH_STAFF_PROF_FLAG" VARCHAR2(1 CHAR) DEFAULT 'N' NOT NULL ENABLE,
"SYNCH_STAFF_PROF_ID" NUMBER
)
Commit;

For:
the package KRSC_EFFORT_DECOMPRESSION

Cause:
The table itg_compressed_elements is missing from the database?

CREATE TABLE "<schema_owner>"."ITG_COMPRESSED_ELEMENTS"
(
"COMPRESSED_ELEMENT_ID" NUMBER NOT NULL ENABLE,
"VERSION" NUMBER NOT NULL ENABLE,
"REPEATS_COUNT" NUMBER NULLABLE ENABLE,
"VALUE" FLOAT NULLABLE ENABLE,
"OWNER_ID" NUMBER NULLABLE ENABLE,
"OWNER_TYPE" VARCHAR2(120) NOT NULL ENABLE,
"COMPRESSED_ENTRY_SEQ" NUMBER NULLABLE ENABLE,
"CREATED_BY" NUMBER NOT NULL ENABLE,
"CREATION_DATE" DATE NOT NULL ENABLE,
"LAST_UPDATED_BY" NUMBER NOT NULL ENABLE,
"LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
)
Commit;

For all SQLS, peplace <schema_owner> by the correct one of course.
ALL of the above have been discussed in defect QCCR1L28634 for the upgrade to PPM 8.

In a nutshell, we found these tables to be existing and empty in a fresh install but they are for whatever reason deleted on an upgrade but only in some strange circumstances, else we’ get these errors reported far more often.

RnDs advice is to NOT run this optionally run script scripts drop_75_objects.sql during the upgrade from 7.5 to 8.0. All the problems associated with the script will be solved in a later Service Pack or Major Release. Currently it’s not possible to give an ETA for this.

Leave a Reply

Skip to toolbar