Corrupt document records issue
We get this error ""The selected Document Content **** has been deleted." for corrupt documents in Teamconnect
Usually we get these errors after DB refresh from Prod to Non prod. It may also occur without DB refresh in some cases.
The issue is that when we create a clone with system documents only we clone the entire document metadata table but only clone system records from j_docu_content.
This leaves the client documents with a metadata record but no corresponding j_docu_content record.
The system drives from the metadata table and expects the rest of the system to be consistent with the data found there.
But in the case of the client documents the expected j_docu_content record is missing and the "The selected Document Content ***** has been deleted." error is generated.
Resolution 1
We create empty j_docu_content records for the client document records.
How to create these empty records:
INSERT INTO J_DOCU_CONTENT ( PRIMARY_KEY, VERSION, DOCUMENT_TYPE, DATA )
SELECT CONTENT_ID, 13, '.', UTL_RAW.CAST_TO_RAW(' ')
FROM (SELECT CONTENT_ID FROM T_DOCUMENT WHERE CONTENT_ID != 0
MINUS
SELECT PRIMARY_KEY FROM J_DOCU_CONTENT);
commit;
Resolution 2
search for the corrupt documents using below query :
select * from t_document where content_id not in (select jd.primary_key from t_document td , j_docu_content jd where td.content_id=jd.primary_key) and content_id >0;
Delete Corrupt document records using below script :
DECLARE
docID NUMBER;
contentID NUMBER;
BEGIN
SELECT PRIMARY_KEY, CONTENT_ID INTO docID, contentID FROM T_DOCUMENT WHERE primary_key = '*****';
UPDATE T_DOCUMENT SET CONTENT_ID = 0 WHERE PRIMARY_KEY = docID;
DELETE FROM E_DOCU_GROUP_ACCESS WHERE ENTERPRISE_OBJECT_ID = docID;
DELETE FROM E_DOCU_USER_ACCESS WHERE ENTERPRISE_OBJECT_ID = docID;
DELETE FROM E_DOCU WHERE DOCUMENT_ID$ = docID;
DELETE FROM E_DOCU_DETAIL WHERE ENTERPRISE_OBJECT_ID = docID;
DELETE T_DOCUMENT WHERE PRIMARY_KEY = docID;
END;