Hi All
I am writing a SQL query that focuses on Document Change Requests that includes their related Documents. While much of the information is easily obtained in the Change Requests table and a few joins to other tables (like Persons), I am NOT able to get the related Document. In writing CAP reports, I used the left and right object types and IDs to get Corrective Actions and their related Condition Reports, and was expecting to use the same approach for this report. However, I am not able to get the correct results in this case.
Where is the "relationship" between Documents and Document Change Requests recorded and stored?
Thanks, Alistair
Hi Alistair I have asked engineering and will let you know as soon as I get a response.
-Derek
Hi Alistair - the table affected_objects could be what you are looking for.
Answer Verified By: Gintautas
Hello
Here is some details that should help you through your process
Table Name
Main Columns
change_request
Hold all information on Change requests (CR)
co_ID - Internal ID used to create relation with other objects.
change_number – Code
work Status – approval status
Documents
Hold all information on Documents
document_id – internal ID used in relation tables
prefix – number
title – title of the document
status - Approval status
trn_id – transaction ID
Affected_objects
Contains relation between CR and object
co_id – change request iD
object_type – affected object type (object types can be queried from base_types table)
object_id – affected object (document_id or other object ID )
change_transacts
Contains relation between change request and all affected objects
co_id – change request
from_trn_id – trn_id of affected object.
to_trn_id – trn_id of new Object
Gintautas