Using P_REFERENCEDCAMOBJECT table

[kulkarni]

This is on 8.4

I am trying to build a sql that shows the eList item name and the current owner. So I was thinkning of joining nodestate with P_REFERENCEDCAMOBJECT. The join criteria is below
Nodestate.dataownerguid = P_REFERENCEDCAMOBJECT.camobjectid

This is however yielding me 0 rows. On debugging I found out that CamIDs for all users stored in a nodestate is different than what is reflected in P_REFERENCEDCAMOBJECT table. An example, for person A it is
CAMID("Default:u:authid=3680887561") in nodestate table
CAMID("Default:r:authid=2087052041") in P_REFRENCEDCAMOBJECT table.

As you can see the authid is different and so it the parameter after 'Default'. In one we have 'u' while in another it is 'r'

Can anyone shed light on how I can use these two tables? Thank you.

[kulkarni]

Anyone with any thoughts on this  ???

[adityashah27]

I dont think these two tables will give you what you need.
P_REFERENCEDCAMOBJECT.camobjectid shows the Roles ('r') and Nodestate.dataownerguid shows userid ('u').

[kulkarni]

Thanks for getting back Aditya.

If that is the case, would you happen to know what tables do I need to get the eList item name and the current owner?

[adityashah27]

one of table is nodestate (for sure) and to get the user names i think you will have to join against Content Store tables props1 & 33.

[kulkarni]

Thank you for your suggestion. I am afraid I was only concentrating on P_tablenames and not CM tables!

Looking at the structure, this is the join criteria I used
cmobjprops1.objguid = nodestate.dataownerguid (strip off the CAMID part from it)
cmobjprops33.cmid = cmobjprops1.cmid

This is nearly perfect and gives me the user id. What we also want is the first name and last name; which I can get from cmobjprops1 table (columns - givenName, surName).

What I am failing to understand is that there are NULL entries in these columns for all the rows in cmobjprops1 table! Is this because we are using Access Manager to control our user classes? Am I supposed to be using some other table to get the names?

Thank you.

[adityashah27]

yes NULL is realted to Acc Man. so you guys are still using Acc Man?
the best option is to join on employee table (it must be available in your DW), in emp table you should get userid, names etc....

[kulkarni]

The app and the Publish DB, both are on a seperate server than the DW. So we will not be able to do the join like you said.

So for this season, I guess we will have to content with userids  :)

Yes we are using Acc Mgr. There are plans to bring it down, but not until for few more months! Thank you for all the help you have given. I have given an "Applaud" for your answer, pertaining to the tables.

I had put this post as a "Question", not sure how to mark it resolved!

[adityashah27]

thanks kulkarni,
FYI... you can create DB links to join db on different servers.