SQL scripts to unlock a Project on Oracle

Answer

Running the following SQL script against the Oracle RPM database will unlock the database:

1. First you need to find the Project ID of the locked project. The name for the Project ID in the database is ELEMENT_ID.
   This query returns the Project ID.
   
   For example:
   
ELEMENT_ID = 5DBACE07117143EC81033485A8F62B07

SELECT ELEMENT_ID
FROM TMT_WBS
WHERE REFERENCE_NUMBER = 'Value of the field Project ID in the Identification Portlet of the Project';

   Example
   
SELECT ELEMENT_ID
FROM TMT_WBS
WHERE REFERENCE_NUMBER = '12345'

2. Run these 2 queries
   
SELECT LEVEL_LOCK, CALCULATE_FLAG, ROLLUP_FLAG
FROM PROJECT_DETAILS
WHERE PROJECT_ID ='<Project ID>';

SELECT REC_STATUS
FROM TMT_WBS
WHERE PROJECT_ID ='<Project ID>' AND REC_STATUS='L';

   
   If one of the values above is not equal to these values, for the specific project, then you can update the table TMT_WBS and PROJECT_DETAILS as reported in step 3:
   
LEVEL_LOCK = 0
CALCULATE_FLAG = 0
ROLLUP_FLAG = 0
REC_STATUS = 'U'

3. Fill out the Project ID into the WHERE clauses of the following queries.
   
UPDATE PROJECT_DETAILS
SET LEVEL_LOCK=0,  
CALCULATE_FLAG=0,
ROLLUP_FLAG=0
WHERE PROJECT_ID ='<Project ID>';

UPDATE TMT_WBS
SET REC_STATUS='U'
WHERE PROJECT_ID='<Project ID>'  
AND REC_STATUS='L';
[</code>]
In our example:

UPDATE PROJECT_DETAILS
SET LEVEL_LOCK=0,  
CALCULATE_FLAG=0,
ROLLUP_FLAG=0
WHERE PROJECT_ID ='5DBACE07117143EC81033485A8F62B07';

UPDATE TMT_WBS
SET REC_STATUS='U'
WHERE PROJECT_ID='5DBACE07117143EC81033485A8F62B07'  
AND REC_STATUS='L';

4. Run these scripts.
5. The RPM project is unlocked now.