Resolution:
How to retrieve the user names of the users who created and modified a record in an external system?

Environment:
=========
TIBCO Product name and version: TIBCO Collaborative Information Manager 7.2.X, 8.0.0
Operating System(s): All Operating Systems

Symptoms:
========
None.

Cause:
=====
None.

Resolution:
=========
The external system ought to have the enterprise name, repository name, record id and record ext to retrieve this information from the TIBCO Collaborative Information Manager (CIM) database instance.

Query to retrieve the user who created a record:

SELECT USERNAME CREATIONUSER FROM MEMBER WHERE ID = (
SELECT MODMEMBERID  FROM PRINCIPALKEY WHERE PRODUCTKEYID =
(SELECT ID FROM PRODUCTKEY WHERE NAME = '&ltPRODUCTID>' AND EXTENSION = '&ltPRODUCTIDEXT>' AND CATALOGID =
(SELECT ID FROM CATALOG WHERE NAME = '&ltMASTER CATALOG NAME>' AND SOURCEORGANIZATIONID = (SELECT ID FROM ORGANIZATION WHERE NAME = '&ltENTERPRISE NAME>')))
and MODVERSION = 1)

Query to retrieve the user who modified a record:

SELECT USERNAME MODIFYUSER FROM MEMBER WHERE ID = (
SELECT MODMEMBERID  FROM PRINCIPALKEY WHERE PRODUCTKEYID =
(SELECT ID FROM PRODUCTKEY WHERE NAME = '&ltPRODUCTID>' AND EXTENSION = '&ltPRODUCTIDEXT>' AND CATALOGID =
(SELECT ID FROM CATALOG WHERE NAME = '&ltMASTER CATALOG NAME>' AND SOURCEORGANIZATIONID = (SELECT ID FROM ORGANIZATION WHERE NAME = '&ltENTERPRISE NAME>')))
and MODVERSION = &ltMODVERSION OF THE RECORD MODIFIED>)

Attachments:
==========
None.

References:
=========
None

How to retrieve the user names of the users who created and modified a record in an external system?