We are currently having two fields in 'Details' section, Attachment (Component type: ATTACHMENT) and URL(Component type: Web Address (URL)).
But now our requirement is to remove these fields from this section, so we are planning to move the attachments & URL present in these fields to the Refernce section using a sql script manually for all existing requests in PPM.
I need to know for doing this, which all tables needs to be updated ?
As far as my analysis, 1. for URL ---> if we insert in KNTA_REFERENCES it is working
2. for Attachments ---> we need to insert in KNTA_DOCUMENTS & KNTA_REFERENCES. Is this enough or some other tables also needs to be updated?
Yes, KNTA_REFERENCES shhould be the Primary tabel for References in case of URL value but you may also want to take a look at KNTA_REF_RELATIONSHIPS_NLS as well to make sure the value is refereced properly.
Moving an attachement may be a little tricky & Risky !
What are you using for your document management in PPM? There could be various ways: simple filesystem storage, Documentum or PPM DMS.
I have never moved an attachment itself ever since we moved off of the filesystem storage to DMS. So, I would strongly suggest you to make sure about this with HP Support so that the move doesn't break any request attacment relationship as some could be critical docs. In either case, Depending on how you are storing and whether or not Versioning is enabled, the attachments in Reference probably would involve KNTA_REFERENCES, KNTA_REF_RELATIONSHIPS_NLS, KNTA_DOCUMENTS, KNTA_DOCUMENT_VERSION, KNTA_DOCUMENT-TIP_CONTENTS, and KNTA_DOCUMENT_HISTORY_CONTENTS tables.
And of couse you should not forget about the KCRT_REQUESTS Table in Both the cases to make sure you don't run into any possible cache issues.
Data from the "Notes" Section is stored in KNTA_NOTE_ENTRIES Table. The column parent_entity_primary_key stores the request id. Here is script you can use to insert notes. Hopefully this can help you ...
INSERT INTO knta_main.KNTA_NOTE_ENTRIES( note_entry_id, parent_entity_primary_key, note, last_update_date, last_updated_by, creation_date, created_by, parent_entity_id, author_id, authored_date, note_context_value, note_context_visible_value) select knta_main.KNTA_NOTE_ENTRIES_S.nextval,request_id ,'Put your Notes here', sysdate - (1/(24 *60*60)), 1, sysdate - (1/(24 *60 *60)), 1, 20, --parent_entity_id for Request is 20 1, sysdate - (1/(24 *60 *60)),30242, 'Request Status Value here' from kcrt_requests where request_id in (Request IDs separated by commas);
When I look at the KNTA_NOTE_ENTRIES table, the NOTE_ENTRY_ID field has a different number used when the note is entered from the request vs. using an INSERT statement with KNTA_NOTE_ENTRIES_S.nextval. Why are there two different numbers used? My concern is that eventually there will be an overlap with these numbers.
The NOTE_ENTRY_ID from the note I entered in the request before my insert from a script was 69974. My script (using KNTA_NOTE_ENTRIES_S.nextval) has the NOTE_ENTRY_ID of 70008. When I enter a note via the request again, the NOTE_ENTRY_ID is 69975. I would have expected it to by 70009 in this case.